package com.aegean.MonitorExamSYS.conduction.dao.Impl;

import java.io.File;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.aegean.MonitorExamSYS.center.dao.CenterDao;
import com.aegean.MonitorExamSYS.conduction.component.PositionsInfo;
import com.aegean.MonitorExamSYS.conduction.dao.ConductionDao;
import com.aegean.MonitorExaxSYS.model.BigType;
import com.aegean.MonitorExaxSYS.model.CenterValue;
import com.aegean.MonitorExaxSYS.model.ConductionValue;
import com.aegean.MonitorExaxSYS.model.ExamInfo;
import com.aegean.MonitorExaxSYS.model.ExamPosition;
import com.aegean.MonitorExaxSYS.model.ExamPositionQualification;
import com.aegean.MonitorExaxSYS.model.ExamineeQualification;
import com.aegean.MonitorExaxSYS.model.ExamineeValue;
import com.aegean.MonitorExaxSYS.model.MessageBox;
import com.aegean.MonitorExaxSYS.model.MonitorValue;
import com.aegean.MonitorExaxSYS.model.SmallType;

@Repository
@Transactional
public class ConductionDaoImpl implements ConductionDao {
	@Resource
	SessionFactory factory;

	@Override
	public ConductionValue insertConduction(ConductionValue one) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			session.save(one);
			return one;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean insertExamPosition(ExamPosition one,
			ConductionValue conduction) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			one.setExamPosition_Unit(conduction);
			session.save(one);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public ConductionValue loginConduction(String login, String pwd) {
		Session session = null;
		ConductionValue one;
		try {
			session = factory.getCurrentSession();
			Criteria c = session.createCriteria(ConductionValue.class);
			c.add(Restrictions.eq("conduction_Login", login));
			c.add(Restrictions.eq("conduction_Password", pwd));
			one = (ConductionValue) c.uniqueResult();
			if (one == null) {
				return null;
			}
			return one;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public int examPositionCount(int Conduction_Id) {
		// TODO Auto-generated method stub
		Session session;
		Criteria cr;
		try {
			session = factory.getCurrentSession();
			cr = session.createCriteria(ExamPosition.class);
			cr.add(Restrictions.eq("examPosition_Unit.conduction_Id",
					Conduction_Id));
			cr.setCacheable(true);
			return cr.list().size();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public List<ExamPosition> ShowExamPositions(int start, int limit,
			int Conduction_Id) {
		Session session;
		Criteria cr;
		try {
			session = factory.getCurrentSession();
			cr = session.createCriteria(ExamPosition.class);
			cr.add(Restrictions.eq("examPosition_Unit.conduction_Id",
					Conduction_Id));
			cr.setFirstResult(start);
			cr.setMaxResults(limit);
			cr.setCacheable(true);
			return cr.list();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean editExamPosition(ExamPosition one, int examPosition_Id) {
		// TODO Auto-generated method stub
		Session session;
		ExamPosition position;
		try {
			session = factory.getCurrentSession();
			position = (ExamPosition) session.load(ExamPosition.class,
					examPosition_Id);
			position.setExamPosition_PersonCount(one
					.getExamPosition_PersonCount());
			position.setExamPosition_State(one.getExamPosition_State());
			position.setExamPosition_Comments(one.getExamPosition_Comments());
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean deleteExamPositions(int[] examPosition_Id) {
		Session session = null;
		try {
			session = factory.getCurrentSession();
			for (int i = 0; i < examPosition_Id.length; i++) {
				ExamPosition position = new ExamPosition();
				Criteria cr = session.createCriteria(ExamPosition.class);
				position = (ExamPosition) cr.add(
						Restrictions.eq("examPosition_Id", examPosition_Id[i]))
						.uniqueResult();
				session.delete(position);
				session.flush();
				session.clear();
			}
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public int examPositionCountQuery(String query, int Conduction_Id) {
		// TODO Auto-generated method stub
		Session session;
		Criteria cr;
		try {
			session = factory.getCurrentSession();
			cr = session.createCriteria(ExamPosition.class);
			cr.add(Restrictions.eq("examPosition_Unit.conduction_Id",
					Conduction_Id));
			cr.add(Restrictions.like("examPosition_Name", "%" + query + "%"));
			cr.setCacheable(true);
			return cr.list().size();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public List<ExamPosition> ShowExamPositionsQuery(String query, int start,
			int limit, int Conduction_Id) {
		Session session;
		Criteria cr;
		try {
			session = factory.getCurrentSession();
			cr = session.createCriteria(ExamPosition.class);
			cr.add(Restrictions.eq("examPosition_Unit.conduction_Id",
					Conduction_Id));
			cr.add(Restrictions.like("examPosition_Name", "%" + query + "%"));
			cr.setFirstResult(start);
			cr.setMaxResults(limit);
			cr.setCacheable(true);
			return cr.list();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public int monitorListCount(DetachedCriteria dcr) {
		// TODO Auto-generated method stub
		Session session;
		Criteria cr;
		try {
			session = factory.getCurrentSession();
			cr = dcr.getExecutableCriteria(session);
			return cr.list().size();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public List<MonitorValue> ShowMonitorListQuery(DetachedCriteria dcr,
			int start, int limit) {
		// TODO Auto-generated method stub
		Session session;
		Criteria cr;
		List<MonitorValue> monitors;
		try {
			session = factory.getCurrentSession();
			cr = dcr.getExecutableCriteria(session);
			cr.setFirstResult(start);
			cr.setMaxResults(limit);
			monitors = cr.list();
			return monitors;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public int bigListCount() {
		Session session = null;
		try {
			session = factory.getCurrentSession();
			Criteria cr = session.createCriteria(BigType.class);
			cr.setCacheable(true);
			int count = cr.list().size();
			return count;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public List<BigType> bigList(int start, int limit) {
		// TODO Auto-generated method stub
		Session session = null;
		List<BigType> bigtypes = null;
		try {
			session = factory.getCurrentSession();
			Criteria cr = session.createCriteria(BigType.class);
			cr.setCacheable(true);
			cr.setFirstResult(start);
			cr.setMaxResults(limit);
			bigtypes = (List<BigType>) cr.list();
			return bigtypes;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean addMonitorValue(MonitorValue monitor, int big_Id) {
		Session session;
		try {
			session = factory.getCurrentSession();
			BigType big = (BigType) session.load(BigType.class, big_Id);
			monitor.setMonitor_Big(big);
			session.save(monitor);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public MonitorValue showMonitorPhoto(int monitor_Id) {
		// TODO Auto-generated method stub
		Session session;
		MonitorValue monitor;
		try {
			session = factory.getCurrentSession();
			monitor = (MonitorValue) session
					.get(MonitorValue.class, monitor_Id);
			return monitor;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean editState(int state, int[] monitor_Ids) {
		Session session = null;
		try {
			session = factory.getCurrentSession();
			for (int i = 0; i < monitor_Ids.length; i++) {
				MonitorValue monitor;
				Criteria cr = session.createCriteria(MonitorValue.class);
				monitor = (MonitorValue) cr.add(
						Restrictions.eq("user_Id", monitor_Ids[i]))
						.uniqueResult();
				monitor.setMonitor_State(state);
				session.flush();
				session.clear();
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public int smallListCount(int small_Id) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			Criteria cr = session.createCriteria(SmallType.class);
			cr.add(Restrictions.eq("small_Big.big_Id", small_Id));
			cr.setCacheable(true);
			return cr.list().size();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public List<SmallType> SmallList(int start, int limit, int small_Id) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			Criteria cr = session.createCriteria(SmallType.class);
			cr.add(Restrictions.eq("small_Big.big_Id", small_Id));
			cr.setFirstResult(start);
			cr.setMaxResults(limit);
			cr.setCacheable(true);
			return cr.list();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean applyForQualification(int state, int Conduction_Id,
			int exam_Id) {
		// TODO Auto-generated method stub
		Session session;
		ExamPositionQualification eq;
		try {
			session = factory.getCurrentSession();
			Criteria cr = session
					.createCriteria(ExamPositionQualification.class);
			cr.add(Restrictions.eq("examPQ_Conduction.conduction_Id",
					Conduction_Id));
			cr.add(Restrictions.eq("examPQ_Exam.examInfo_Id", exam_Id));
			cr.setCacheable(true);
			eq = (ExamPositionQualification) cr.uniqueResult();
			if (eq == null) {
				eq = new ExamPositionQualification();
				ExamInfo exam = (ExamInfo) session
						.load(ExamInfo.class, exam_Id);
				ConductionValue conduction = (ConductionValue) session.load(
						ConductionValue.class, Conduction_Id);
				eq.setExamPQ_Exam(exam);
				eq.setExamPQ_Conduction(conduction);
				eq.setExamPQ_State(state);
				session.save(eq);
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean writeMessage(int conduction_Id, String theme, String contents) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			MessageBox box = new MessageBox();
			box.setMessage_Date(new Date());
			box.setMessage_State(1);// 相对于校方是发信
			box.setMessage_Theme(theme);
			box.setMessage_Contents(contents);
			box.setMessage_IdBefore(0);// 没有上一节邮箱
			ConductionValue one = (ConductionValue) session.load(
					ConductionValue.class, conduction_Id);
			box.setMessage_From(one);
			session.save(box);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<MessageBox> showMessages(int state, int status,
			int conduction_Id, int start, int limit) {
		Connection cn;
		PreparedStatement ps;
		ResultSet rs;
		List<MessageBox> messages = new ArrayList<MessageBox>();
		Session session;
		String hql = null;
		try {
			session = factory.getCurrentSession();
			if (state == 1) {// 收件箱
				hql = "select m.*,c.conduction_name as place_Name,b.message_theme as message_BeforeTheme from messagebox m left join messagebox b on m.message_idbefore=b.message_id ,conductionvalue as c where m.message_from=c.conduction_id and c.conduction_Id="
						+ conduction_Id
						+ " and m.message_State=2  order by m.message_Date desc limit "
						+ start + "," + limit + "";
			} else if (state == 2) {// 发件箱
				hql = "select m.*,c.conduction_name as place_Name,b.message_theme as message_BeforeTheme from messagebox m left join messagebox b on m.message_idbefore=b.message_id ,conductionvalue as c where m.message_from=c.conduction_id and c.conduction_Id="
						+ conduction_Id
						+ " and m.message_State=1  order by m.message_Date desc limit "
						+ start + "," + limit + "";
			} else if (state == 3) {// 指定高校
				hql = "select m.*,c.conduction_name as place_Name,b.message_theme as message_BeforeTheme from messagebox m left join messagebox b on m.message_idbefore=b.message_id ,conductionvalue as c where m.message_from=c.conduction_id and c.conduction_Id="
						+ conduction_Id
						+ " order by m.message_Date desc limit "
						+ start
						+ ","
						+ limit + "";
			} else if (state == 4) {
				hql = "select m.*,c.conduction_name as place_Name,b.message_theme as message_BeforeTheme from messagebox m left join messagebox b on m.message_idbefore=b.message_id ,conductionvalue as c where m.message_from=c.conduction_id and DATE_SUB(CURDATE(), INTERVAL 1 WEEK) <= date(m.message_Date) and c.conduction_Id="
						+ conduction_Id
						+ " order by m.message_Date desc limit "
						+ start
						+ ","
						+ limit + "";
			} else if (state == 5) {
				hql = "select m.*,c.conduction_name as place_Name,b.message_theme as message_BeforeTheme from messagebox m left join messagebox b on m.message_idbefore=b.message_id ,conductionvalue as c where m.message_from=c.conduction_id and c.conduction_Id="
						+ conduction_Id
						+ " order by m.message_Date desc limit "
						+ start
						+ ","
						+ limit + "";
			}
			cn = session.connection();
			ps = cn.prepareStatement(hql);
			rs = ps.executeQuery();
			while (rs.next()) {
				MessageBox one = new MessageBox();
				one.setMessage_Id(rs.getInt(1));
				one.setMessage_Theme(rs.getString(2));
				one.setMessage_Contents(rs.getString(3));
				one.setMessage_Date(rs.getDate(4));
				one.setMessage_IdBefore(rs.getInt(5));
				ConductionValue c = new ConductionValue();
				c.setConduction_Id(rs.getInt(7));
				one.setMessage_From(c);
				one.setMessage_State(rs.getInt(6));
				one.setPlace_Name(rs.getString(8));
				if (rs.getString(9) == null || rs.getString(9).equals("")) {
					one.setMessage_BeforeTheme("无");
				} else {
					one.setMessage_BeforeTheme(rs.getString(9));
				}
				messages.add(one);
			}
			return messages;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public boolean replyMessage(int conduction_Id, String theme,
			String contents, int beforeId) {
		Session session;
		try {
			session = factory.getCurrentSession();
			MessageBox box = new MessageBox();
			box.setMessage_Date(new Date());
			box.setMessage_State(1);// 相对于校方是发信
			box.setMessage_Theme(theme);
			box.setMessage_Contents(contents);
			box.setMessage_IdBefore(beforeId);
			ConductionValue one = (ConductionValue) session.load(
					ConductionValue.class, conduction_Id);
			box.setMessage_From(one);
			session.save(box);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<PositionsInfo> showPositions(int small_Id, int condu) {
		// TODO Auto-generated method stub
		Session session;
		Query q;
		List<PositionsInfo> positions = new ArrayList<PositionsInfo>();
		String sql = "SELECT pq.PQ_ID,exam.EXAMINFO_NAME,eq.EXAMPOSITION_NAME	FROM examinfo AS exam,examposition AS eq,exampositionqualification AS eqs,positionqualification AS pq,smalltype AS small WHERE exam.EXAMINFO_TYPE=small.SMALL_ID AND eqs.EXAMPQ_EXAM=exam.EXAMINFO_ID AND pq.PQ_EPQ=eqs.EXAMPQ_ID AND pq.PQ_EP=eq.EXAMPOSITION_ID AND  small.SMALL_ID=:small_Id AND eq.EXAMPOSITION_UNIT=:condu";
		try {
			session = factory.getCurrentSession();
			q = session.createSQLQuery(sql);
			q.setInteger("small_Id", small_Id);
			q.setInteger("condu", condu);
			List obgs = q.list();
			for (int i = 0; i < obgs.size(); i++) {
				Object[] results = (Object[]) obgs.get(i);
				PositionsInfo one = new PositionsInfo();
				one.setPositionInfo_Id(i + 1);
				one.setPositionQ_Id(Integer.parseInt(results[0].toString()));
				one.setExam_Name(results[1].toString());
				one.setPosition_Name(results[2].toString());
				positions.add(one);
			}
			return positions;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}
}
